﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Web.UI;
using Antu.WebControls;
using Antu.Core.Components;
using Antu.Core.Services;
using System.Web.UI.WebControls;
using EluZSProvider;
using System.Web;
using System.Data;

namespace WebSys.UI.Control.ZhengShou
{
    public class ZS_YUEBAOTJControl : MPBasicTemplate
    {
        EluZSDataProvider _EluZSDataProvider = (EluZSDataProvider)ProviderService.ProviderServiceMange.GetProvider("EluZSProvider");
        DropDownList DropDownList1, DropDownList2;
        MPCalendar MPCalendar1;
        Button btnSeach;
        MPRepeater MPRepeaterList;
        public string userid = string.Empty;
        protected override void OnLoad(EventArgs e)
        {
            if (HttpContext.Current.Session["UserID"] == null)
            {
                Antu.Utilities.UtilitiesPage.AlertDialogClear("登录超时，请重新登录！", "window.parent.location.href('../login.aspx');");
            }
            else
            {
                userid = HttpContext.Current.Session["UserID"].ToString();
            }
            base.OnLoad(e);
            DropDownList1 = (DropDownList)FindControl("DropDownList1");
            DropDownList2 = (DropDownList)FindControl("DropDownList2");
            MPCalendar1 = (MPCalendar)FindControl("MPCalendar1");
            btnSeach = (Button)FindControl("btnSeach");
            MPRepeaterList = (MPRepeater)FindControl("MPRepeaterList");
            if (!base.Page.IsPostBack) 
            {
                QSDataDLL();
                DropDownList1.SelectedValue = System.DateTime.Now.Year.ToString();
                DropDownList2.SelectedValue = System.DateTime.Now.Month.ToString();
                MPCalendar1.Text = GetLastYearMonth();
                BindData();
            }
            btnSeach.Click += new EventHandler(btnSeach_Click);
        }
        protected string GetLastYearMonth() {
            string temp = string.Empty;
            string t1 = System.DateTime.Now.Year.ToString();
            int t11 = Convert.ToInt32(t1);
            temp = (t11 - 1).ToString() + "-12-20";
            return temp;
        }
        protected string GetLastMonth() {
            string temp = string.Empty;
            string strYEAR = string.Empty;
            string strMONTH = string.Empty;
            string t1 = System.DateTime.Now.Year.ToString();
            string t2 = System.DateTime.Now.Month.ToString();
            string t3 = System.DateTime.Now.Day.ToString();
            int t11 = Convert.ToInt32(t1);
            int t21 = Convert.ToInt32(t2);
            if (t21 == 1)
            {
                strYEAR = (t11 - 1).ToString();
                strMONTH = "12";
            }
            else 
            {
                strYEAR = t1;
                strMONTH = (t21 - 1).ToString();
            }
            if (!string.IsNullOrEmpty(strYEAR) && !string.IsNullOrEmpty(strMONTH)) 
            {
                temp = strYEAR + "-" + strMONTH + "-20";
            }
            return temp;
        }
        protected void btnSeach_Click(object sender, EventArgs e)
        {
            BindData();
        }
        
        protected void QSDataDLL()
        {
            string strYEAR = System.DateTime.Now.Year.ToString();
            string strSSYEAR = System.Configuration.ConfigurationManager.AppSettings["SSYEAR"].ToString();
            int ss = Convert.ToInt32(strSSYEAR);
            List<string> lyear = new List<string>();
            int y1 = Convert.ToInt32(strYEAR);//2013
            int ye1 = y1 + 1;//2014
            int yc = ye1 - ss;//4
            if (yc > 0)
            {
                for (int i = 0; i < yc; i++)
                {
                    if (!lyear.Contains(strSSYEAR)) { lyear.Add(strSSYEAR); }
                    lyear.Add((2010 + i + 1).ToString());
                }
            }
            else
            {
                lyear.Add(strSSYEAR);
            }
            DropDownList1.DataSource = lyear;
            DropDownList1.DataBind();
        }

        protected void BindData()
        {
            string strNian = DropDownList1.SelectedValue;
            string strYue = DropDownList2.SelectedValue;
            string strQSDate = MPCalendar1.Text;
            DataTable dt = GetYBBTable(strYue, strNian, strQSDate);
            MPRepeaterList.DataSource = dt;
            MPRepeaterList.DataBind();
        }
        protected DataTable GetYBBTable(string strMonth,string strYear,string strDate) 
        {
            //string strQJZ = "'玄武区','鼓楼区','建邺区','秦淮区','雨花台区','栖霞区','江宁区','浦口区','六合区','溧水县','高淳县'";
            //string strQJ9 = "'玄武区','鼓楼区','建邺区','秦淮区','雨花台区','栖霞区','江宁区','浦口区','六合区'";
            //string strQJ6 = "'玄武区','鼓楼区','建邺区','秦淮区','雨花台区','栖霞区'";
            //string strQJ3 = "'江宁区','浦口区','六合区'";
            //string strQJ2 = "'溧水县','高淳县'";
            //string strBZ = "搬迁完毕";
            //string strZ = "总计";
            //string str9 = "九区小计";
            //string str6 = "江南六区小计";
            //string str3 = "三区小计";
            //string str2 = "两区小计";
            string strQJZ = System.Configuration.ConfigurationManager.AppSettings["strQJZ"].ToString();
            string strQJ9 = System.Configuration.ConfigurationManager.AppSettings["strQJ9"].ToString();
            string strQJ6 = System.Configuration.ConfigurationManager.AppSettings["strQJ6"].ToString();
            string strQJ3 = System.Configuration.ConfigurationManager.AppSettings["strQJ3"].ToString();
            string strQJ2 = System.Configuration.ConfigurationManager.AppSettings["strQJ2"].ToString();
            string strBZ = System.Configuration.ConfigurationManager.AppSettings["strBZ"].ToString();
            string strZ = System.Configuration.ConfigurationManager.AppSettings["strZ"].ToString();
            string str9 = System.Configuration.ConfigurationManager.AppSettings["str9"].ToString();
            string str6 = System.Configuration.ConfigurationManager.AppSettings["str6"].ToString();
            string str3 = System.Configuration.ConfigurationManager.AppSettings["str3"].ToString();
            string str2 = System.Configuration.ConfigurationManager.AppSettings["str2"].ToString();
            string strWhere = string.Empty;
            if (!string.IsNullOrEmpty(strDate)) 
            {
                strWhere = " And 征收期限1 > to_date('" + strDate + "','yyyy-MM-dd') ";
            }
            DataTable dt = new DataTable();
            string sql = string.Format(@"Select t.keyname,
(Select count(Distinct 项目名称) From zs_ybbtj Where qs=t.keyvalue And Month='{1}' And Year='{0}') 当年在拆总量项目个数,
(Select nvl(Sum(居民户数),0) From zs_ybbtj Where qs=t.keyvalue And Month='{1}' And Year='{0}') 当年在拆居民户数,
(Select nvl(Sum(工企单位),0) From zs_ybbtj Where qs=t.keyvalue And Month='{1}' And Year='{0}') 当年在拆工企单位,
(Select nvl(Sum(征收总面积),0) From zs_ybbtj Where qs=t.keyvalue And Month='{1}' And Year='{0}') 当年在拆建筑面积,
(Select count(Distinct 项目名称) From zs_ybbtj Where qs=t.keyvalue And Month='{1}' And Year='{0}' {2}) 当年批准总量项目个数,
(Select nvl(Sum(居民户数),0) From zs_ybbtj Where qs=t.keyvalue And Month='{1}' And Year='{0}' {2}) 当年批准居民户数,
(Select nvl(Sum(工企单位),0) From zs_ybbtj Where qs=t.keyvalue And Month='{1}' And Year='{0}' {2}) 当年批准工企单位,
(Select nvl(Sum(征收总面积),0) From zs_ybbtj Where qs=t.keyvalue And Month='{1}' And Year='{0}' {2}) 当年批准建筑面积,
(Select count(Distinct 项目名称) From zs_ybbtj Where qs=t.keyvalue And Month='{1}' And Year='{0}' And 备注 Like '%{8}%') 当年已完成数量项目个数,
(Select nvl(Sum(已搬居民户数),0) From zs_ybbtj Where qs=t.keyvalue And Month='{1}' And Year='{0}' ) 当年已完成居民户数,
(Select nvl(Sum(已搬工企单位),0) From zs_ybbtj Where qs=t.keyvalue And Month='{1}' And Year='{0}' ) 当年已完成工企单位,
(Select nvl(Sum(已搬面积),0) From zs_ybbtj Where qs=t.keyvalue And Month='{1}' And Year='{0}' ) 当年已完成建筑面积,
(Select count(Distinct 项目名称) From zs_ybbtj Where qs=t.keyvalue And Month='{1}' And Year='{0}' And (备注 not Like '%{8}%' Or 备注 Is Null )) 结转下月续拆数量项目个数,
(Select nvl(Sum(未搬居民户数),0) From zs_ybbtj Where qs=t.keyvalue And Month='{1}' And Year='{0}' ) 结转下月续拆居民户数,
(Select nvl(Sum(未搬工企单位),0) From zs_ybbtj Where qs=t.keyvalue And Month='{1}' And Year='{0}' ) 结转下月续拆工企单位,
(Select nvl(Sum(未搬面积),0) From zs_ybbtj Where qs=t.keyvalue And Month='{1}' And Year='{0}' ) 结转下月续拆建筑面积
 From zs_params t Where Name='区属' 
And keyname In ({5}) 
Union All 
Select '{11}',
(Select count(Distinct 项目名称) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({5})) And Month='{1}' And Year='{0}') 当年在拆总量项目个数,
(Select nvl(Sum(居民户数),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({5})) And Month='{1}' And Year='{0}') 当年在拆居民户数,
(Select nvl(Sum(工企单位),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({5})) And Month='{1}' And Year='{0}') 当年在拆工企单位,
(Select nvl(Sum(征收总面积),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({5})) And Month='{1}' And Year='{0}') 当年在拆建筑面积,
(Select count(Distinct 项目名称) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({5})) And Month='{1}' And Year='{0}' {2}) 当年批准总量项目个数,
(Select nvl(Sum(居民户数),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({5})) And Month='{1}' And Year='{0}' {2}) 当年批准居民户数,
(Select nvl(Sum(工企单位),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({5})) And Month='{1}' And Year='{0}' {2}) 当年批准工企单位,
(Select nvl(Sum(征收总面积),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({5})) And Month='{1}' And Year='{0}' {2}) 当年批准建筑面积,
(Select count(Distinct 项目名称) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({5})) And Month='{1}' And Year='{0}'  And 备注 Like '%{8}%') 当年已完成数量项目个数,
(Select nvl(Sum(已搬居民户数),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({5})) And Month='{1}' And Year='{0}' ) 当年已完成居民户数,
(Select nvl(Sum(已搬工企单位),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({5})) And Month='{1}' And Year='{0}' ) 当年已完成工企单位,
(Select nvl(Sum(已搬面积),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({5})) And Month='{1}' And Year='{0}' ) 当年已完成建筑面积,
(Select count(Distinct 项目名称) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({5})) And Month='{1}' And Year='{0}' And (备注 not Like '%{8}%' Or 备注 Is Null )) 结转下月续拆数量项目个数,
(Select nvl(Sum(未搬居民户数),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({5})) And Month='{1}' And Year='{0}' ) 结转下月续拆居民户数,
(Select nvl(Sum(未搬工企单位),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({5})) And Month='{1}' And Year='{0}' ) 结转下月续拆工企单位,
(Select nvl(Sum(未搬面积),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({5})) And Month='{1}' And Year='{0}' ) 结转下月续拆建筑面积
 From dual 
Union All 
Select t.keyname,
(Select count(Distinct 项目名称) From zs_ybbtj Where qs=t.keyvalue And Month='{1}' And Year='{0}') 当年在拆总量项目个数,
(Select nvl(Sum(居民户数),0) From zs_ybbtj Where qs=t.keyvalue And Month='{1}' And Year='{0}') 当年在拆居民户数,
(Select nvl(Sum(工企单位),0) From zs_ybbtj Where qs=t.keyvalue And Month='{1}' And Year='{0}') 当年在拆工企单位,
(Select nvl(Sum(征收总面积),0) From zs_ybbtj Where qs=t.keyvalue And Month='{1}' And Year='{0}') 当年在拆建筑面积,
(Select count(Distinct 项目名称) From zs_ybbtj Where qs=t.keyvalue And Month='{1}' And Year='{0}' {2}) 当年批准总量项目个数,
(Select nvl(Sum(居民户数),0) From zs_ybbtj Where qs=t.keyvalue And Month='{1}' And Year='{0}' {2}) 当年批准居民户数,
(Select nvl(Sum(工企单位),0) From zs_ybbtj Where qs=t.keyvalue And Month='{1}' And Year='{0}' {2}) 当年批准工企单位,
(Select nvl(Sum(征收总面积),0) From zs_ybbtj Where qs=t.keyvalue And Month='{1}' And Year='{0}' {2}) 当年批准建筑面积,
(Select count(Distinct 项目名称) From zs_ybbtj Where qs=t.keyvalue And Month='{1}' And Year='{0}') 当年已完成数量项目个数,
(Select nvl(Sum(已搬居民户数),0) From zs_ybbtj Where qs=t.keyvalue And Month='{1}' And Year='{0}' And 备注 Like '%{8}%') 当年已完成居民户数,
(Select nvl(Sum(已搬工企单位),0) From zs_ybbtj Where qs=t.keyvalue And Month='{1}' And Year='{0}') 当年已完成工企单位,
(Select nvl(Sum(已搬面积),0) From zs_ybbtj Where qs=t.keyvalue And Month='{1}' And Year='{0}' ) 当年已完成建筑面积,
(Select count(Distinct 项目名称) From zs_ybbtj Where qs=t.keyvalue And Month='{1}' And Year='{0}' And (备注 not Like '%{8}%' Or 备注 Is Null)) 结转下月续拆数量项目个数,
(Select nvl(Sum(未搬居民户数),0) From zs_ybbtj Where qs=t.keyvalue And Month='{1}' And Year='{0}' ) 结转下月续拆居民户数,
(Select nvl(Sum(未搬工企单位),0) From zs_ybbtj Where qs=t.keyvalue And Month='{1}' And Year='{0}' ) 结转下月续拆工企单位,
(Select nvl(Sum(未搬面积),0) From zs_ybbtj Where qs=t.keyvalue And Month='{1}' And Year='{0}' ) 结转下月续拆建筑面积
 From zs_params t Where Name='区属' 
And keyname In ({6}) 
Union All 
Select '{12}',
(Select count(Distinct 项目名称) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({6})) And Month='{1}' And Year='{0}') 当年在拆总量项目个数,
(Select nvl(Sum(居民户数),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({6})) And Month='{1}' And Year='{0}') 当年在拆居民户数,
(Select nvl(Sum(工企单位),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({6})) And Month='{1}' And Year='{0}') 当年在拆工企单位,
(Select nvl(Sum(征收总面积),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({6})) And Month='{1}' And Year='{0}') 当年在拆建筑面积,
(Select count(Distinct 项目名称) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({6})) And Month='{1}' And Year='{0}' {2}) 当年批准总量项目个数,
(Select nvl(Sum(居民户数),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({6})) And Month='{1}' And Year='{0}' {2}) 当年批准居民户数,
(Select nvl(Sum(工企单位),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({6})) And Month='{1}' And Year='{0}' {2}) 当年批准工企单位,
(Select nvl(Sum(征收总面积),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({6})) And Month='{1}' And Year='{0}' {2}) 当年批准建筑面积,
(Select count(Distinct 项目名称) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({6})) And Month='{1}' And Year='{0}' And 备注 Like '%{8}%') 当年已完成数量项目个数,
(Select nvl(Sum(已搬居民户数),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({6})) And Month='{1}' And Year='{0}' ) 当年已完成居民户数,
(Select nvl(Sum(已搬工企单位),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({6})) And Month='{1}' And Year='{0}' ) 当年已完成工企单位,
(Select nvl(Sum(已搬面积),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({6})) And Month='{1}' And Year='{0}' ) 当年已完成建筑面积,
(Select count(Distinct 项目名称) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({6})) And Month='{1}' And Year='{0}' And (备注 not Like '%{8}%' Or 备注 Is Null)) 结转下月续拆数量项目个数,
(Select nvl(Sum(未搬居民户数),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({6})) And Month='{1}' And Year='{0}' ) 结转下月续拆居民户数,
(Select nvl(Sum(未搬工企单位),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({6})) And Month='{1}' And Year='{0}' ) 结转下月续拆工企单位,
(Select nvl(Sum(未搬面积),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({6})) And Month='{1}' And Year='{0}' ) 结转下月续拆建筑面积
 From dual 
Union All 
Select '{10}',
(Select count(Distinct 项目名称) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({4})) And Month='{1}' And Year='{0}') 当年在拆总量项目个数,
(Select nvl(Sum(居民户数),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({4})) And Month='{1}' And Year='{0}') 当年在拆居民户数,
(Select nvl(Sum(工企单位),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({4})) And Month='{1}' And Year='{0}') 当年在拆工企单位,
(Select nvl(Sum(征收总面积),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({4})) And Month='{1}' And Year='{0}') 当年在拆建筑面积,
(Select count(Distinct 项目名称) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({4})) And Month='{1}' And Year='{0}' {2}) 当年批准总量项目个数,
(Select nvl(Sum(居民户数),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({4})) And Month='{1}' And Year='{0}' {2}) 当年批准居民户数,
(Select nvl(Sum(工企单位),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({4})) And Month='{1}' And Year='{0}' {2}) 当年批准工企单位,
(Select nvl(Sum(征收总面积),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({4})) And Month='{1}' And Year='{0}' {2}) 当年批准建筑面积,
(Select count(Distinct 项目名称) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({4})) And Month='{1}' And Year='{0}' And 备注 Like '%{8}%') 当年已完成数量项目个数,
(Select nvl(Sum(已搬居民户数),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({4})) And Month='{1}' And Year='{0}' ) 当年已完成居民户数,
(Select nvl(Sum(已搬工企单位),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({4})) And Month='{1}' And Year='{0}' ) 当年已完成工企单位,
(Select nvl(Sum(已搬面积),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({4})) And Month='{1}' And Year='{0}' ) 当年已完成建筑面积,
(Select count(Distinct 项目名称) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({4})) And Month='{1}' And Year='{0}' And (备注 not Like '%{8}%' Or 备注 Is Null)) 结转下月续拆数量项目个数,
(Select nvl(Sum(未搬居民户数),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({4})) And Month='{1}' And Year='{0}' ) 结转下月续拆居民户数,
(Select nvl(Sum(未搬工企单位),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({4})) And Month='{1}' And Year='{0}' ) 结转下月续拆工企单位,
(Select nvl(Sum(未搬面积),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({4})) And Month='{1}' And Year='{0}' ) 结转下月续拆建筑面积
 From dual 
Union All 
Select t.keyname,
(Select count(Distinct 项目名称) From zs_ybbtj Where qs=t.keyvalue And Month='{1}' And Year='{0}') 当年在拆总量项目个数,
(Select nvl(Sum(居民户数),0) From zs_ybbtj Where qs=t.keyvalue And Month='{1}' And Year='{0}') 当年在拆居民户数,
(Select nvl(Sum(工企单位),0) From zs_ybbtj Where qs=t.keyvalue And Month='{1}' And Year='{0}') 当年在拆工企单位,
(Select nvl(Sum(征收总面积),0) From zs_ybbtj Where qs=t.keyvalue And Month='{1}' And Year='{0}') 当年在拆建筑面积,
(Select count(Distinct 项目名称) From zs_ybbtj Where qs=t.keyvalue And Month='{1}' And Year='{0}' {2}) 当年批准总量项目个数,
(Select nvl(Sum(居民户数),0) From zs_ybbtj Where qs=t.keyvalue And Month='{1}' And Year='{0}' {2}) 当年批准居民户数,
(Select nvl(Sum(工企单位),0) From zs_ybbtj Where qs=t.keyvalue And Month='{1}' And Year='{0}' {2}) 当年批准工企单位,
(Select nvl(Sum(征收总面积),0) From zs_ybbtj Where qs=t.keyvalue And Month='{1}' And Year='{0}' {2}) 当年批准建筑面积,
(Select count(Distinct 项目名称) From zs_ybbtj Where qs=t.keyvalue And Month='{1}' And Year='{0}' And 备注 Like '%{8}%') 当年已完成数量项目个数,
(Select nvl(Sum(已搬居民户数),0) From zs_ybbtj Where qs=t.keyvalue And Month='{1}' And Year='{0}' ) 当年已完成居民户数,
(Select nvl(Sum(已搬工企单位),0) From zs_ybbtj Where qs=t.keyvalue And Month='{1}' And Year='{0}' ) 当年已完成工企单位,
(Select nvl(Sum(已搬面积),0) From zs_ybbtj Where qs=t.keyvalue And Month='{1}' And Year='{0}' ) 当年已完成建筑面积,
(Select count(Distinct 项目名称) From zs_ybbtj Where qs=t.keyvalue And Month='{1}' And Year='{0}' And (备注 not Like '%{8}%' Or 备注 Is Null)) 结转下月续拆数量项目个数,
(Select nvl(Sum(未搬居民户数),0) From zs_ybbtj Where qs=t.keyvalue And Month='{1}' And Year='{0}' ) 结转下月续拆居民户数,
(Select nvl(Sum(未搬工企单位),0) From zs_ybbtj Where qs=t.keyvalue And Month='{1}' And Year='{0}' ) 结转下月续拆工企单位,
(Select nvl(Sum(未搬面积),0) From zs_ybbtj Where qs=t.keyvalue And Month='{1}' And Year='{0}' ) 结转下月续拆建筑面积
 From zs_params t Where Name='区属' 
And keyname In ({7}) 
Union All 
Select '{13}',
(Select count(Distinct 项目名称) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({7})) And Month='{1}' And Year='{0}') 当年在拆总量项目个数,
(Select nvl(Sum(居民户数),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({7})) And Month='{1}' And Year='{0}') 当年在拆居民户数,
(Select nvl(Sum(工企单位),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({7})) And Month='{1}' And Year='{0}') 当年在拆工企单位,
(Select nvl(Sum(征收总面积),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({7})) And Month='{1}' And Year='{0}') 当年在拆建筑面积,
(Select count(Distinct 项目名称) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({7})) And Month='{1}' And Year='{0}' {2}) 当年批准总量项目个数,
(Select nvl(Sum(居民户数),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({7})) And Month='{1}' And Year='{0}' {2}) 当年批准居民户数,
(Select nvl(Sum(工企单位),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({7})) And Month='{1}' And Year='{0}' {2}) 当年批准工企单位,
(Select nvl(Sum(征收总面积),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({7})) And Month='{1}' And Year='{0}' {2}) 当年批准建筑面积,
(Select count(Distinct 项目名称) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({7})) And Month='{1}' And Year='{0}' And 备注 Like '%{8}%') 当年已完成数量项目个数,
(Select nvl(Sum(已搬居民户数),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({7})) And Month='{1}' And Year='{0}' ) 当年已完成居民户数,
(Select nvl(Sum(已搬工企单位),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({7})) And Month='{1}' And Year='{0}' ) 当年已完成工企单位,
(Select nvl(Sum(已搬面积),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({7})) And Month='{1}' And Year='{0}' ) 当年已完成建筑面积,
(Select count(Distinct 项目名称) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({7})) And Month='{1}' And Year='{0}' And (备注 not Like '%{8}%' Or 备注 Is Null)) 结转下月续拆数量项目个数,
(Select nvl(Sum(未搬居民户数),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({7})) And Month='{1}' And Year='{0}' ) 结转下月续拆居民户数,
(Select nvl(Sum(未搬工企单位),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({7})) And Month='{1}' And Year='{0}' ) 结转下月续拆工企单位,
(Select nvl(Sum(未搬面积),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({7})) And Month='{1}' And Year='{0}' ) 结转下月续拆建筑面积
 From dual 
Union All 
Select '{9}',
(Select count(Distinct 项目名称) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({3})) And Month='{1}' And Year='{0}') 当年在拆总量项目个数,
(Select nvl(Sum(居民户数),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({3})) And Month='{1}' And Year='{0}') 当年在拆居民户数,
(Select nvl(Sum(工企单位),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({3})) And Month='{1}' And Year='{0}') 当年在拆工企单位,
(Select nvl(Sum(征收总面积),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({3})) And Month='{1}' And Year='{0}') 当年在拆建筑面积,
(Select count(Distinct 项目名称) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({3})) And Month='{1}' And Year='{0}' {2}) 当年批准总量项目个数,
(Select nvl(Sum(居民户数),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({3})) And Month='{1}' And Year='{0}' {2}) 当年批准居民户数,
(Select nvl(Sum(工企单位),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({3})) And Month='{1}' And Year='{0}' {2}) 当年批准工企单位,
(Select nvl(Sum(征收总面积),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({3})) And Month='{1}' And Year='{0}' {2}) 当年批准建筑面积,
(Select count(Distinct 项目名称) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({3})) And Month='{1}' And Year='{0}' And 备注 Like '%{8}%') 当年已完成数量项目个数,
(Select nvl(Sum(已搬居民户数),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({3})) And Month='{1}' And Year='{0}' ) 当年已完成居民户数,
(Select nvl(Sum(已搬工企单位),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({3})) And Month='{1}' And Year='{0}' ) 当年已完成工企单位,
(Select nvl(Sum(已搬面积),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({3})) And Month='{1}' And Year='{0}' ) 当年已完成建筑面积,
(Select count(Distinct 项目名称) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({3})) And Month='{1}' And Year='{0}' And (备注 not Like '%{8}%' Or 备注 Is Null)) 结转下月续拆数量项目个数,
(Select nvl(Sum(未搬居民户数),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({3})) And Month='{1}' And Year='{0}' ) 结转下月续拆居民户数,
(Select nvl(Sum(未搬工企单位),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({3})) And Month='{1}' And Year='{0}' ) 结转下月续拆工企单位,
(Select nvl(Sum(未搬面积),0) From zs_ybbtj Where qs In (Select keyvalue From zs_params Where Name='区属' 
And keyname In ({3})) And Month='{1}' And Year='{0}' ) 结转下月续拆建筑面积
 From dual ", strYear, strMonth, strWhere, strQJZ, strQJ9, strQJ6, strQJ3, strQJ2, strBZ, strZ, str9, str6, str3, str2);
            dt = _EluZSDataProvider.GetTable(sql);
            return dt;
        }
    }
}


